const mysql = require('mysql2/promise');
const config = require('./config');
const fs = require('fs').promises;
const path = require('path');

async function initDatabase() {
  let connection;
  
  try {
    // 首先连接到MySQL服务器（不指定数据库）
    connection = await mysql.createConnection({
      host: config.db.host,
      port: config.db.port,
      user: config.db.user,
      password: config.db.password
    });
    
    console.log('连接到MySQL服务器成功');
    
    // 创建数据库（如果不存在）
    await connection.execute(`CREATE DATABASE IF NOT EXISTS ${config.db.database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log(`数据库 ${config.db.database} 创建成功或已存在`);
    
    // 使用指定数据库
    await connection.execute(`USE ${config.db.database}`);
    
    // 读取并执行SQL文件
    const usersSqlPath = path.join(__dirname, 'users.sql');
    const samplesSqlPath = path.join(__dirname, 'sample.sql');
    
    // 执行用户表SQL
    const usersSql = await fs.readFile(usersSqlPath, 'utf8');
    const usersStatements = usersSql.split(';').filter(stmt => stmt.trim());
    
    for (const statement of usersStatements) {
      if (statement.trim()) {
        await connection.execute(statement);
      }
    }
    console.log('用户表创建成功');
    
    // 执行样品表SQL
    const samplesSql = await fs.readFile(samplesSqlPath, 'utf8');
    await connection.execute(samplesSql);
    console.log('样品表创建成功');
    
    // 创建默认管理员用户（密码：123456）
    const crypto = require('crypto');
    const hashedPassword = crypto.createHash('sha256').update('123456').digest('hex');
    
    await connection.execute(`
      INSERT INTO users (username, name, email, password, role, status) 
      VALUES (?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE 
        name = VALUES(name),
        email = VALUES(email),
        password = VALUES(password),
        role = VALUES(role),
        status = VALUES(status)
    `, ['admin', '管理员', 'admin@example.com', hashedPassword, 'admin', 'active']);
    
    console.log('默认管理员用户创建成功（用户名：admin，密码：123456）');
    
    console.log('数据库初始化完成！');
    
  } catch (error) {
    console.error('数据库初始化失败:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  initDatabase();
}

module.exports = { initDatabase }; 